
package com.york.controller;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.york.entity.User;
import com.york.listener.UserListener;
import com.york.model.dto.excel.DynamicHeader;
import com.york.model.vo.DownloadExcelData;
import com.york.service.UserService;
import com.york.util.EasyExcelUtil;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;


/**
 * @author YORK
 */
@RestController
public class EasyExcelController {
    @Autowired
    private UserService userService;
    @Autowired
    private UserListener userListener;


    /**
     * @description 文件上传的功能
     * http://localhost:8084/import01
     */
    @GetMapping("import01")
    public String import01(MultipartFile file) {
        try (InputStream is = file.getInputStream()) {
            EasyExcel.read(is, User.class, userListener).sheet().doRead();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "success";
    }

    /**
     * @description 文件下载的功能，没有使用模板来操作下载
     * http://localhost:8084/export02
     */
    @GetMapping("export02")
    public void export02(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream()) {
            List<User> users = userService.selectList();
            String fileName = "用户列表01.xlsx";
            //文件类型是xls
            // String fileName = "用户列表01.xls";
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            EasyExcel.write(os, User.class).sheet("用户列表").doWrite(users);
            //指定文件类型，文件名要对应
            // EasyExcel.write(os, UserDTO.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @description 文件下载的功能，使用模板来操作下载，单个用户的导出
     * http://localhost:8084/export03
     */
    @GetMapping("export03")
    public void export03(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream();
             InputStream is = this.getClass().getClassLoader().getResourceAsStream("template/单用户列表.xlsx");
        ) {
            User user = userService.selectOne();
            String fileName = "单用户列表.xlsx";
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(os, User.class).withTemplate(is);
            ExcelWriterSheetBuilder sheetBuilder = excelWriterBuilder.sheet();
            sheetBuilder.doFill(user);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @description 文件下载的功能，使用模板来操作下载，多个用户的导出，向下填充数据
     * http://localhost:8084/export04
     */
    @GetMapping("export04")
    public void export04(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream();
             InputStream is = this.getClass().getClassLoader().getResourceAsStream("template/多用户列表.xlsx");
        ) {
            List<User> users = userService.selectList();
            String fileName = "多用户列表.xlsx";
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(os, User.class).withTemplate(is);
            ExcelWriterSheetBuilder sheetBuilder = excelWriterBuilder.sheet();
            sheetBuilder.doFill(users);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @description 文件下载的功能，使用模板来操作下载，组合用户的导出，向下填充数据，必须要做到强制换行
     * http://localhost:8084/export05
     */
    @GetMapping("export05")
    public void export05(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream();
             InputStream is = this.getClass().getClassLoader().getResourceAsStream("template/组合用户列表.xlsx");
        ) {
            List<User> users = userService.selectList();
            String fileName = "组合用户列表.xlsx";
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            FillConfig build = FillConfig.builder().forceNewRow(true).build();
            ExcelWriter workBook = EasyExcel.write(os, User.class).withTemplate(is).build();
            WriteSheet sheet = EasyExcel.writerSheet().build();
            HashMap<String, String> map = new HashMap<>(2);
            map.put("count", "5000");
            workBook.fill(users, build, sheet);
            workBook.fill(map, sheet);
            workBook.finish();
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @description 文件下载的功能，使用模板来操作下载，组合用户的导出，向右填充数据
     * http://localhost:8084/export06
     */
    @GetMapping("export06")
    public void export06(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream();
             InputStream is = this.getClass().getClassLoader().getResourceAsStream("template/水平多用户列表.xlsx");
        ) {
            List<User> users = userService.selectList();
            String fileName = "水平多用户列表.xlsx";
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            FillConfig build = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            ExcelWriter workBook = EasyExcel.write(os, User.class).withTemplate(is).build();
            WriteSheet sheet = EasyExcel.writerSheet().build();
            workBook.fill(users, build, sheet);
            workBook.finish();
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }



    @GetMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response) {

        String fileName = "人员信息模板.xlsx";

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
//
        Map<String, String[]> map = new HashMap<>();
        map.put("city", new String[] {"北京","上海","深圳","南京","杭州","合肥"});

        try {
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + "");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            Integer[] headWidth = new Integer[] {14,14,14,14,14,14,14,14,14,14, 30};	// 自定义列宽
            WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(DownloadExcelData.class, headWidth, 0, "人员信息", map);
            WriteSheet writeSheetOptions = EasyExcelUtil.writeSelectedSheet(getHeader(), 1, "信息采集");
            //写
            excelWriter.write(new ArrayList<String>(), writeSheet);
            excelWriter.write(new ArrayList<String>(), writeSheetOptions);
            //手动关闭
            excelWriter.finish();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    private List<DynamicHeader> getHeader(){
        List<DynamicHeader> headers = new ArrayList<>();
        String[] titles = new String[] {"籍贯","学历","是否婚配","爱好","信息来源","详细地址"};
        for(int i=0;i<titles.length;i++) {
            DynamicHeader header = new DynamicHeader();
            header.setName(titles[i]);
            if(i==0) {
                header.setSelectedSource(new String[] {"北京市","上海市","广州省","浙江省","安徽省"});
            }else if(i==1) {
                header.setSelectedSource(new String[] {"小学","初中","高中","中专","高职","专科","本科","硕士研究生","博士研究生"});
            }
            headers.add(header);
        }
        if(headers.size() > 0) {
            DynamicHeader header = new DynamicHeader();
            header.setName("用户编号");
            headers.add(0, header);
        }
        return headers;
    }
}





